package cn.com.callback

import cn.com.elite.EliteBean
import org.json.JSONException
import org.slf4j.Logger
import org.slf4j.LoggerFactory
import org.springframework.jdbc.core.JdbcTemplate

/**
 * 华盛通给客户添加标签
 */
public class autoHSTCustLabel implements CallBackInvoke{
    private static final Logger log = LoggerFactory.getLogger(autoHSTCustLabel.class);

    public void doCallBack(EliteBean eliteBean, String callBack, Map map, String paramString) {

        // 获取数据源
        JdbcTemplate jt = new JdbcTemplate(eliteBean.getDataSource());
        def Customerid;
        def MaxTime;

        //处理assign_his表 将历史跟进的过的数据 写入 assign_his_Follow_up_person
        try {
            String insertSQL = " insert into assign_his_Follow_up_person(CUSTOMER_GUID,OBJECTIVE_GUID,HANDLEGROUP_ID,HANDLEBY_ID)\n" +
                    " select DISTINCT ash.CUSTOMER_GUID,ash.OBJECTIVE_GUID,ash.OLDHANDLEGROUP_ID,ash.OLDHANDLEBY_ID\n" +
                    " from assign_his ash\n" +
                    " LEFT JOIN assign_his_Follow_up_person ashfup on ash.OBJECTIVE_GUID=ashfup.OBJECTIVE_GUID and ash.CUSTOMER_GUID=ashfup.CUSTOMER_GUID and ash.OLDHANDLEBY_ID=ashfup.HANDLEBY_ID\n" +
                    " where 1=1 \n" +
                    " and ASSIGNDATE> NOW()-INTERVAL 2 HOUR\n" +
                    " and ashfup.HANDLEBY_ID is null\n" +
                    " and ash.OLDHANDLEBY_ID is not null and ash.OLDHANDLEBY_ID !='' and ash.OLDHANDLEBY_ID !='null'\n" +
                    " and ash.OLDHANDLEGROUP_ID is not null and ash.OLDHANDLEGROUP_ID !='' and ash.OLDHANDLEBY_ID !='null'\n" +
                    " and ash.CUSTOMER_GUID is not null and ash.CUSTOMER_GUID !=''\n" +
                    " and ash.OBJECTIVE_GUID is not null and ash.OBJECTIVE_GUID !=''";
            def insertSQLresult= eliteBean.update(insertSQL, "dataSource");
            log.debug("assign_his_Follow_up_person 数据更新标识："+insertSQLresult);
        } catch (Exception e) {
            log.error("assign_his_Follow_up_person 数据更新标识处理异常，失败：" + e.getMessage());
        }

        //用户app端申请服顾
        try {
            //List gwapplylist = eliteBean.chaxun_sql("SELECT hg.Rec_Id, hg.Customerid FROM hst_gwapply_msg_notice hg LEFT JOIN customer c ON c.customer_guid = hg.customerid WHERE 1 = 1 AND hg.deal_flag = 1 AND c.gwapply_status IS NULL ORDER BY hg.createdate DESC ","dataSource");
            List gwapplylist = eliteBean.chaxun_sql("SELECT hg.Rec_Id, hg.Customerid FROM hst_gwapply_msg_notice hg LEFT JOIN customer c ON c.customer_guid = hg.customerid WHERE 1 = 1 AND hg.deal_flag = 1 AND c.gwapply_status IS NULL AND hg.createdate < NOW()-INTERVAL 2 HOUR ORDER BY hg.createdate DESC ","dataSource");
            for (int i = 0; i < gwapplylist.size(); i++) {
                Customerid = gwapplylist.get(i).get("Customer_Guid");
                String updateCustSQL = "UPDATE customer SET gwapply_status = '1' WHERE customer_guid = '" + Customerid + "'";
                def dataresult= eliteBean.update(updateCustSQL, "dataSource");
            }
        } catch (Exception e) {
            log.error("用户app端申请服顾 处理异常，失败：" + e.getMessage());
        }

        //用户联系客服申请服顾
        try {
            //List workordertypelist = eliteBean.chaxun_sql("SELECT hw.customer_guid FROM hst_workorder hw LEFT JOIN hst_workorderlist hwl ON hw.workorder_guid = hwl.workorder_guid LEFT JOIN customer c ON hw.customer_guid = c.customer_guid WHERE 1 = 1 AND hwl.hst_workordertype1 = 'S50000' AND hwl.hst_workordertype2 = 'S50200' AND hwl.hst_workordertype3 = 'S50204' AND c.workordertype_status IS NOT NULL  ORDER BY hw.createdate DESC ","dataSource");
            List workordertypelist = eliteBean.chaxun_sql("SELECT hw.customer_guid FROM hst_workorder hw LEFT JOIN hst_workorderlist hwl ON hw.workorder_guid = hwl.workorder_guid LEFT JOIN customer c ON hw.customer_guid = c.customer_guid WHERE 1 = 1 AND hwl.hst_workordertype1 = 'S50000' AND hwl.hst_workordertype2 = 'S50200' AND hwl.hst_workordertype3 = 'S50204' AND c.workordertype_status IS NOT NULL AND hw.createdate < NOW()-INTERVAL 2 HOUR ORDER BY hw.createdate DESC ","dataSource");
            for (int i = 0; i < workordertypelist.size(); i++) {
                Customerid = workordertypelist.get(i).get("Customer_Guid");
                String updateCustSQL = "UPDATE customer SET workordertype_status = '1' WHERE customer_guid = '" + Customerid + "'";
                def dataresult= eliteBean.update(updateCustSQL, "dataSource");
            }
        } catch (Exception e) {
            log.error("用户联系客服申请服顾 处理异常，失败：" + e.getMessage());
        }

        //参与活动的用户
        try {
            //List activitystatuslist = eliteBean.chaxun_sql("SELECT customer_guid, max( createddate ) AS createddate FROM elitetask_activity WHERE activity_state = 1 GROUP BY customer_guid ","dataSource");
            List activitystatuslist = eliteBean.chaxun_sql("SELECT customer_guid, max( createddate ) AS createddate FROM elitetask_activity WHERE activity_state = 1 AND createddate < NOW()-INTERVAL 2 HOUR GROUP BY customer_guid ","dataSource");
            for (int i = 0; i < activitystatuslist.size(); i++) {
                Customerid = activitystatuslist.get(i).get("customer_guid");
                String updateCustSQL = "UPDATE customer SET activity_status = '1' WHERE customer_guid = '" + Customerid + "'";
                def dataresult= eliteBean.update(updateCustSQL, "dataSource");
            }
        } catch (Exception e) {
            log.error("参与活动的用户 处理异常，失败：" + e.getMessage());
        }

        //接入在线网聊
        try {
            //List chatrequeststatuslist = eliteBean.chaxun_sql("SELECT client_id, max(creation_time) AS creation_time FROM chat_request GROUP BY client_id","dataSource");
            List chatrequeststatuslist = eliteBean.chaxun_sql("SELECT client_id, max(creation_time) AS creation_time FROM chat_request WHERE FROM_UNIXTIME( creation_time / 1000, '%Y-%m-%d %H:%i:%s' ) < NOW()-INTERVAL 2 HOUR GROUP BY client_id ","dataSource");
            for (int i = 0; i < chatrequeststatuslist.size(); i++) {
                Customerid = chatrequeststatuslist.get(i).get("client_id");
                MaxTime= chatrequeststatuslist.get(i).get("creation_time");
                String updateCustSQL = "UPDATE customer SET lastchattime = FROM_UNIXTIME( " + MaxTime + " / 1000, '%Y-%m-%d %H:%i:%s' ), latestbehaviortype = CASE WHEN latestbehaviortime IS NULL THEN 1 WHEN latestbehaviortime < FROM_UNIXTIME( " + MaxTime + " / 1000, '%Y-%m-%d %H:%i:%s' ) THEN 1 ELSE latestbehaviortype END, latestbehaviortime = CASE WHEN latestbehaviortime IS NULL THEN FROM_UNIXTIME( " + MaxTime + " / 1000, '%Y-%m-%d %H:%i:%s' ) WHEN latestbehaviortime < FROM_UNIXTIME( " + MaxTime + " / 1000, '%Y-%m-%d %H:%i:%s' ) THEN FROM_UNIXTIME( " + MaxTime + " / 1000, '%Y-%m-%d %H:%i:%s' ) ELSE latestbehaviortime END WHERE customer_guid = '" + Customerid + "'";
                def dataresult= eliteBean.update(updateCustSQL, "dataSource");
            }
        } catch (Exception e) {
            log.error("接入在线网聊 处理异常，失败：" + e.getMessage());
        }

        //电话呼入且已接听
        try {
            //List calltype1statuslist = eliteBean.chaxun_sql("SELECT customer_guid, max( callstarttime ) AS workstarttime FROM event WHERE calltype = 1 AND callestablished = 1 GROUP BY customer_guid ","dataSource");
            List calltype1statuslist = eliteBean.chaxun_sql("SELECT customer_guid, max( callstarttime ) AS workstarttime FROM event WHERE calltype = 1 AND callestablished = 1 AND callstarttime < NOW()-INTERVAL 2 HOUR GROUP BY customer_guid ","dataSource");
            for (int i = 0; i < calltype1statuslist.size(); i++) {
                Customerid = calltype1statuslist.get(i).get("customer_guid");
                MaxTime= calltype1statuslist.get(i).get("workstarttime");
                String updateCustSQL = "UPDATE customer SET ibontime = '" + MaxTime + "', latestbehaviortype = CASE WHEN latestbehaviortime IS NULL THEN 2 WHEN latestbehaviortime < '" + MaxTime + "' THEN 2 ELSE latestbehaviortype END, latestbehaviortime = CASE WHEN latestbehaviortime IS NULL THEN '" + MaxTime + "' WHEN latestbehaviortime < '" + MaxTime + "' THEN '" + MaxTime + "' ELSE latestbehaviortime END WHERE customer_guid = '" + Customerid + "'";
                def dataresult= eliteBean.update(updateCustSQL, "dataSource");
            }
        } catch (Exception e) {
            log.error("电话呼入且已接听 处理异常，失败：" + e.getMessage());
        }

        //电话呼出且已接听
        try {
            //List calltype2statuslist = eliteBean.chaxun_sql("SELECT customer_guid, max( callstarttime ) AS workstarttime FROM event WHERE calltype = 2 AND callestablished = 1 GROUP BY customer_guid ","dataSource");
            List calltype2statuslist = eliteBean.chaxun_sql("SELECT customer_guid, max( callstarttime ) AS workstarttime FROM event WHERE calltype = 2 AND callestablished = 1 AND callstarttime < NOW()-INTERVAL 2 HOUR GROUP BY customer_guid ","dataSource");
            for (int i = 0; i < calltype2statuslist.size(); i++) {
                Customerid = calltype2statuslist.get(i).get("customer_guid");
                MaxTime= calltype2statuslist.get(i).get("workstarttime");
                String updateCustSQL = "UPDATE customer SET obontime = '" + MaxTime + "', latestbehaviortype = CASE WHEN latestbehaviortime IS NULL THEN 3 WHEN latestbehaviortime < '" + MaxTime + "' THEN 3 ELSE latestbehaviortype END, latestbehaviortime = CASE WHEN latestbehaviortime IS NULL THEN '" + MaxTime + "' WHEN latestbehaviortime < '" + MaxTime + "' THEN '" + MaxTime + "' ELSE latestbehaviortime END WHERE customer_guid = '" + Customerid + "'";
                def dataresult= eliteBean.update(updateCustSQL, "dataSource");
            }
        } catch (Exception e) {
            log.error("电话呼出且已接听 处理异常，失败：" + e.getMessage());
        }

/*
        //企微回消息 --赵处理
        try {
            List wechatreceiverstatuslist = eliteBean.chaxun_sql("select hc.customer_guid from hst_chat_msg_receiver hc left join customer c on c.customer_guid = hc.customer_guid Where c.wechat_receiver_status is null order by hc.Add_WeChat_date desc ","dataSource");
            for (int i = 0; i < wechatreceiverstatuslist.size(); i++) {
                Customerid = wechatreceiverstatuslist.get(i).get("customer_guid");
                String updateCustSQL = "Update customer Set wechat_receiver_status = '1' Where customer_guid = '" + Customerid + "'";
                eliteBean.update(updateCustSQL, "dataSource");
            }
        } catch (Exception e) {
            log.error("企微回消息 处理异常，失败：" + e.getMessage());
        }
*/
/*
        //客户标签A、B、C、D
        try {
            String[] data = new String[2];
            data[0] = "INSERT INTO c_codetables(Code_Guid, Code_ID, Code_Name, Use_Type, Use_Status, Createdby, Createddate, Remark, level, Parentpath ) SELECT UUID(), tab_activity_id, tab_activity_name, '回收用户再分配-优先级', '1', 'SELITE', NOW(), concat('AND elitetask_activity.tab_activity_id = ', '''', tab_activity_id, ''''), 'elitetask_activity', concat(' elitetask_activity.tab_activity_id = ', '''', tab_activity_id, '''') FROM hst_Activity WHERE 1 = 1 AND tab_task_type ='2' AND tab_activity_starttime <= NOW() AND tab_activity_endtime <= NOW() AND tab_activity_id NOT IN (SELECT c.Code_ID FROM c_codetables c LEFT JOIN hst_Activity h ON c.Code_ID = h.tab_activity_id WHERE 1 = 1 AND c.Use_Type='回收用户再分配-优先级' AND c.Use_Status = '1' AND h.tab_activity_id IS NOT NULL) ";
            data[1] = "UPDATE c_codetables AS b LEFT JOIN hst_Activity AS a ON b.Code_ID = a.tab_activity_id SET b.code_Name = a.tab_activity_name, b.modifiedby = 'SELITE', b.modifieddate = NOW() WHERE 1 = 1 AND b.Use_Type='回收用户再分配-优先级' AND b.Use_Status = '1'AND a.tab_task_type ='2' AND a.tab_activity_starttime <= NOW() AND a.tab_activity_endtime <= NOW() AND b.Code_Name != a.tab_activity_name ";
            int[] dataresult = eliteBean.updateBatch(data, "dataSource");
            } catch (Exception e) {
            log.error("客户标签A、B、C、D 处理异常，失败：" + e.getMessage());
        }
*/

    }
}

